﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SQLite;

namespace SEO.DAL
{
    /// <summary>
    /// 版权所有: 版权所有(C) 2011，上海云里网络科技有限公司
    /// 内容摘要: 电子邮件分组数据库
    /// 完成日期：2011年9月12日
    /// 版    本：V1.0 
    /// 作    者：Adin
    /// </summary>
    public class EDMSQLITEGroup
    {

        /// <summary>
        /// 插入分组名称
        /// </summary>
        /// <param name="strGroupName">分组名称</param>
        /// <param name="iUserID">用户ID</param>
        public static void InsertGroup(string strGroupName,int iUserID)
        {
            try
            {
                SQLiteConnection cn = new SQLiteConnection(SEO.DBSqlHelp.EDMDBHelper.strEDMEMailsConn);
                cn.SetPassword(SEO.Utility.CommonHelper.SQLITEPASSWORD);
                cn.Open();
                string strSql = "insert into EmailGroup(group_id,group_name,user_id,add_date) " +
                    " values(" + GetAutoAddNum() + ",'" + strGroupName + "'," + iUserID + ",datetime('now','localtime'))";
                SQLiteCommand cm = new SQLiteCommand(strSql, cn);
                cm.ExecuteNonQuery();
                cn.Close();
            }
            catch (Exception ex)
            {
                SEO.Utility.Log.SaveErrorLog("插入分组名称 -> " + ex.Message);
            }
        }

        /// <summary>
        /// 更新分组名称
        /// </summary>
        /// <param name="strGroupName"></param>
        /// <param name="iGroupID"></param>
        /// <param name="strPostStatus"></param>
        public static void UpdateGroup(string strGroupName, int iGroupID,string strPostStatus)
        {
            try
            {
                SQLiteConnection cn = new SQLiteConnection(SEO.DBSqlHelp.EDMDBHelper.strEDMEMailsConn);
                cn.SetPassword(SEO.Utility.CommonHelper.SQLITEPASSWORD);
                cn.Open();
                string strSql = "update EmailGroup set group_name='" + strGroupName + "',post_status='" + strPostStatus + "' where group_id=" + iGroupID;
                SQLiteCommand cm = new SQLiteCommand(strSql, cn);
                cm.ExecuteNonQuery();
                cn.Close();
            }
            catch (Exception ex)
            {
                SEO.Utility.Log.SaveErrorLog("更新分组名称 -> " + ex.Message);
            }
        }

        /// <summary>
        /// 更新分组对应的邮件数量
        /// </summary>
        /// <param name="strGroupName">分组名称</param>
        /// <param name="iGroupNum">邮件数量</param>
        public static void UpdateGroupNum(string strGroupName, int iGroupNum)
        {
            try
            {
                SQLiteConnection cn = new SQLiteConnection(SEO.DBSqlHelp.EDMDBHelper.strEDMEMailsConn);
                cn.SetPassword(SEO.Utility.CommonHelper.SQLITEPASSWORD);
                cn.Open();
                string strSql = "update EmailGroup set group_num = group_num + " + iGroupNum + " where group_name='" + strGroupName + "'";
                SQLiteCommand cm = new SQLiteCommand(strSql, cn);
                cm.ExecuteNonQuery();
                cn.Close();
            }
            catch (Exception ex)
            {
                SEO.Utility.Log.SaveErrorLog("更新分组对应的邮件数量 -> " + ex.Message);
            }
        }

        /// <summary>
        /// 删除分组
        /// </summary>
        /// <param name="strGroupID">分组ID</param>
        public static void DeleteGroup(string strGroupID)
        {
            try
            {
                SQLiteConnection cn = new SQLiteConnection(SEO.DBSqlHelp.EDMDBHelper.strEDMEMailsConn);
                cn.SetPassword(SEO.Utility.CommonHelper.SQLITEPASSWORD);
                cn.Open();
                string strSql = "delete from EmailGroup where group_id=" + strGroupID;
                SQLiteCommand cm = new SQLiteCommand(strSql, cn);
                cm.ExecuteNonQuery();
                cn.Close();
            }
            catch (Exception ex)
            {
                SEO.Utility.Log.SaveErrorLog("删除分组 -> " + ex.Message);
            }
        }

        /// <summary>
        /// 删除所有分组数据
        /// </summary>
        public static void DeleteAllGroup()
        {
            try
            {
                SQLiteConnection cn = new SQLiteConnection(SEO.DBSqlHelp.EDMDBHelper.strEDMEMailsConn);
                cn.SetPassword(SEO.Utility.CommonHelper.SQLITEPASSWORD);
                cn.Open();
                string strSql = "delete from EmailGroup";
                SQLiteCommand cm = new SQLiteCommand(strSql, cn);
                cm.ExecuteNonQuery();
                cn.Close();
            }
            catch (Exception ex)
            {
                SEO.Utility.Log.SaveErrorLog("删除所有分组 -> " + ex.Message);
            }
        }

        /// <summary>
        /// Group获取最大ID
        /// </summary>
        /// <returns></returns>
        public static int GetAutoAddNum()
        {
            int iNum = 0;
            try
            {
                SQLiteConnection cn = new SQLiteConnection(SEO.DBSqlHelp.EDMDBHelper.strEDMEMailsConn);
                cn.SetPassword(SEO.Utility.CommonHelper.SQLITEPASSWORD);
                cn.Open();
                string strSql = "select group_id from EmailGroup order by group_id desc limit 1";
                SQLiteCommand cm = new SQLiteCommand(strSql, cn);
                SQLiteDataReader dr = cm.ExecuteReader();
                if (dr.Read())
                {
                    iNum = int.Parse(dr["group_id"].ToString()) + 1;
                }
                else
                {
                    iNum = 1;
                }
                dr.Dispose();
                cn.Close();
            }
            catch (Exception ex)
            {
                SEO.Utility.Log.SaveErrorLog("Group获取最大ID -> " + ex.Message);
            }

            return iNum;
        }

        /// <summary>
        /// 根据GroupID获取分组名称
        /// </summary>
        /// <param name="strGroupName">分组名称</param>
        /// <returns></returns>
        public static int GetGroupIDByGroupName(string strGroupName)
        {
            int iNum = 0;
            try
            {
                SQLiteConnection cn = new SQLiteConnection(SEO.DBSqlHelp.EDMDBHelper.strEDMEMailsConn);
                cn.SetPassword(SEO.Utility.CommonHelper.SQLITEPASSWORD);
                cn.Open();
                string strSql = "select group_id from EmailGroup where group_name='" + strGroupName + "'";
                SQLiteCommand cm = new SQLiteCommand(strSql, cn);
                SQLiteDataReader dr = cm.ExecuteReader();
                if (dr.Read())
                {
                    iNum = int.Parse(dr["group_id"].ToString());
                }
                else
                {
                    iNum = 0;
                }
                dr.Dispose();
                cn.Close();
            }
            catch (Exception ex)
            {
                SEO.Utility.Log.SaveErrorLog("获取GroupName名称 -> " + ex.Message);
            }

            return iNum;
        }

        /// <summary>
        /// 检测分组名称是否存在
        /// </summary>
        /// <param name="strGroupName"></param>
        /// <returns></returns>
        public static bool CheckMailGroupIsExist(string strGroupName)
        {
            bool bValue = false;
            try
            {
                SQLiteConnection cn = new SQLiteConnection(SEO.DBSqlHelp.EDMDBHelper.strEDMEMailsConn);
                cn.SetPassword(SEO.Utility.CommonHelper.SQLITEPASSWORD);
                cn.Open();
                string strSql = "select group_id from EmailGroup where group_name='" + strGroupName + "'";
                SQLiteCommand cm = new SQLiteCommand(strSql, cn);
                SQLiteDataReader dr = cm.ExecuteReader();
                if (dr.Read())
                {
                    bValue = true;
                }
                else
                {
                    bValue = false;
                }
                dr.Dispose();
                cn.Close();
            }
            catch (Exception ex)
            {
                SEO.Utility.Log.SaveErrorLog("检测是否已存在数据库中 -> " + ex.Message);
            }

            return bValue;
        }


        /// <summary>
        /// 绑定表EDMMailGroup到DataGridView
        /// <param name="dgv">DataGridView控件名称</param>
        /// <param name="strSql">SQL语句</param>
        /// </summary>
        public static void BindEDMMailGroupDAL(System.Windows.Forms.DataGridView dgv, string strSql)
        {
            try
            {
                //清除最后自动生成的行
                dgv.AutoGenerateColumns = false;
                SQLiteConnection cn = new SQLiteConnection(SEO.DBSqlHelp.EDMDBHelper.strEDMEMailsConn);
                cn.SetPassword(SEO.Utility.CommonHelper.SQLITEPASSWORD);
                cn.Open();
                SQLiteCommand cm = new SQLiteCommand(strSql, cn);
                SQLiteDataAdapter da = new SQLiteDataAdapter(cm);
                DataSet ds = new DataSet();
                da.Fill(ds, "EmailGroup");
                ds.Tables["EmailGroup"].Columns.Add("ShowDate", typeof(string));
                ds.Tables["EmailGroup"].Columns.Add("PostStatus", typeof(string));
                ds.Tables["EmailGroup"].Columns.Add("ShowNum", typeof(string));
                for (int i = 0; i < ds.Tables["EmailGroup"].Rows.Count; i++)
                {
                    DataRow dr = ds.Tables["EmailGroup"].Rows[i];
                    if (dr["post_status"].ToString() == "1")
                    {
                        dr["PostStatus"] = "未加入队列";
                    }
                    else
                    {
                        dr["PostStatus"] = "已加入队列";
                    }
                    dr["ShowDate"] = Convert.ToDateTime(dr["add_date"].ToString()).ToString("yyyy.MM.dd");
                    string strGetCoudNum = SEO.DAL.EDMSQLITEEmails.CountEmailNum(" where group_id=" + dr["group_id"].ToString()).ToString();
                    string strGetPostMails = SEO.DAL.EDMSQLITEEmails.CountEmailNum(" where post_status='1' and group_id=" + dr["group_id"].ToString()).ToString();
                    string strGetNoPostMails = SEO.DAL.EDMSQLITEEmails.CountEmailNum(" where post_status='0' and group_id=" + dr["group_id"].ToString()).ToString();
                    dr["ShowNum"] = "共[" + strGetCoudNum + "],(已发送" + strGetPostMails + ",未发送" + strGetNoPostMails + ")双击查看";
                }
                dgv.DataSource = ds.Tables["EmailGroup"].DefaultView;
                da.Dispose();
                cn.Close();
                //清除当前选择行行为
                dgv.CurrentCell = null;

            }
            catch (Exception ex)
            {
                SEO.Utility.Log.SaveErrorLog("绑定EDMMailGroup -> " + ex.Message);
            }
        }

        /// <summary>
        /// 填充ComboBoxEx控件
        /// </summary>
        /// <param name="cb">ComboBoxEx控件</param>
        /// <param name="TableName">表名</param>
        /// <param name="strValue">填充字段</param>
        public static void FillDataToComboBox(System.Windows.Forms.ComboBox cb, string TableName, string strValue)
        {
            SQLiteConnection cn = new SQLiteConnection(SEO.DBSqlHelp.EDMDBHelper.strEDMEMailsConn);
            cn.SetPassword(SEO.Utility.CommonHelper.SQLITEPASSWORD);
            cn.Open();
            string strSql = "select " + strValue + " from " + TableName;
            SQLiteCommand cm = new SQLiteCommand(strSql, cn);
            SQLiteDataReader dr = cm.ExecuteReader();
            while (dr.Read())
            {
                string strName = dr[strValue].ToString();
                cb.Items.Add(strName);
            }
            dr.Dispose();
            cn.Close();
        }

        /// <summary>
        /// 更新分组发布状态
        /// </summary>
        /// <param name="strGetID">分组ID</param>
        /// <param name="strStatus">状态：1为未加入，0为加入</param>
        public static void UpdateGroupStatus(string strGetID,string strStatus)
        {
            try
            {
                SQLiteConnection cn = new SQLiteConnection(SEO.DBSqlHelp.EDMDBHelper.strEDMEMailsConn);
                cn.SetPassword(SEO.Utility.CommonHelper.SQLITEPASSWORD);
                cn.Open();
                string strSql = "update EmailGroup set post_status=" + strStatus + " where group_id=" + strGetID;
                SQLiteCommand cm = new SQLiteCommand(strSql, cn);
                cm.ExecuteNonQuery();
                cn.Close();
            }
            catch (Exception ex)
            {
                SEO.Utility.Log.SaveErrorLog("更新分组发布状态 -> " + ex.Message);
            }
        }

    }
}
